<?php
set_time_limit(0);
//new_num20210409.csv
//"num","code","province","city","city_code","operator","operator_name","data_status","data_from"



start(); // 执行脚本

/**
 * 执行
 */
function start()
{
	echo '===写入新的号段脚本程序，开始执行===' . "\n";

	$file = fopen('new_num20210409.csv', 'r');
	fgetcsv_reg($file); //跳过第一行
	$no = 0; //插入计数
	$total = 0;//总记录数
	//$rno = 0; //处理的行数
	
	echo '开始提取CSV文件数据...' . "\n";
	$rows = get_csv_rows($file);
	$now_nums = get_now_nums();
	$csv_nums = get_csv_nums($rows);
	log_exist_data($csv_nums, $now_nums, $rows); //记录重复的号段
	$diff = array_diff($csv_nums, $now_nums); //数据库中不存在的号段
	//dd($diff);
	
	if ($diff) {
		foreach ($diff as $num) {
			$total ++;
			$row = $rows[$num];
			$num = $row[0];
			$city = $row[2] . ' ' . $row[3];
			$code = $row[1];
			$province = $row[2];
			$area = $row[3];
			$new_data[] = "('$num', '$city', '$code', '$province', '$area', '', '')";
			$no ++;
		}
		echo '提取CSV文件数据结束，开始插入新数据' . "\n";
		batch_add_to_db($new_data);
		echo "共 $total 条数据，新增 $no 条" . "\n";
	} else {
		echo '没有新的数据需要插入' . "\n";
	}
	
	echo '===写入新的号段脚本程序，结束===' . "\n";
	exit;
}

/**
 * 连接数据库
 */
function conn_mysql() 
{
	$servername = "localhost";
	$dbname = 'mobile_local'; //mobile
	$port = 3306; //63306
	$username = "root";
	$password = "12345678";
	$dsn="mysql:host=$servername;port=$port;dbname=$dbname";
	try {
		$conn = new PDO($dsn, $username, $password);
		return $conn;
	}
	catch(PDOException $e)
	{
		echo $e->getMessage();
		die();
	}
}


//读取解析一行数据
function fgetcsv_reg(& $handle, $length = null, $d = ',', $e = '"') 
{
	$d = preg_quote($d);
	$e = preg_quote($e);
	$_line = "";
	$eof=false;
	while ($eof != true) {
		$_line .= (empty ($length) ? fgets($handle) : fgets($handle, $length));
		$itemcnt = preg_match_all('/' . $e . '/', $_line, $dummy);
		if ($itemcnt % 2 == 0)
			$eof = true;
	}
	$_csv_line = preg_replace('/(?: |[ ])?$/', $d, trim($_line));
	$_csv_pattern = '/(' . $e . '[^' . $e . ']*(?:' . $e . $e . '[^' . $e . ']*)*' . $e . '|[^' . $d . ']*)' . $d . '/';
	preg_match_all($_csv_pattern, $_csv_line, $_csv_matches);
	$_csv_data = $_csv_matches[1];
	for ($_csv_i = 0; $_csv_i < count($_csv_data); $_csv_i++) {
		$_csv_data[$_csv_i] = preg_replace('/^' . $e . '(.*)' . $e . '$/s', '$1', $_csv_data[$_csv_i]);
		$_csv_data[$_csv_i] = str_replace($e . $e, $e, $_csv_data[$_csv_i]);
	}
	return empty ($_line) ? false : $_csv_data;
}

/*
$file = fopen('new_num20210409.csv', 'r');
while (! feof($file)) {
	$row = fgetcsv($file, 8, ',', '"');
	dd($row);
}
fclose($file);
*/

function dd($str) 
{
	echo '<pre>';
	print_r($str);
	echo '</pre>';
}

function get_csv_rows($file) 
{
	$rows = [];
	while (! feof($file)) {
		$row = fgetcsv_reg($file);
		 $rows[$row[0]] = $row;
	}
	return $rows;
}

function get_now_nums() 
{
	$db = conn_mysql();
	$mobi_nums = [];
	$table_name = 'location';
	foreach ($db->query('SELECT num FROM ' . $table_name) as $row) {
		$mobi_nums[] = $row['num'];
	}
	$db = null;
	return $mobi_nums;
}

function get_csv_nums($rows) 
{
	foreach ($rows as $row) {
		$nums[] = $row[0];
	}
	return $nums;
}

/**
 * 重复号段日志
 */
function log_exist_data($csv_nums, $now_nums, $csv_rows) 
{
	$count = 0;
	$both = array_intersect($csv_nums, $now_nums); //重复的号段
	if ($both) {
		$diff_file = fopen('diff_test.csv', 'w');
		$txt = 'num,code,province,city,city_code,operator,operator_name,data_status,data_from' . "\n";
		fwrite($diff_file, $txt);
		foreach ($both as $num) {
			$row = implode(',', $csv_rows[$num]);
			$txt = "$row\n";
			fwrite($diff_file, $txt);
			$count ++;
		}
		fclose($diff_file);
		echo '共有' . $count . '条已存在的号段，请在文件diff_test.csv中查看' . "\n";
	} else {
		echo '没有重复的号段' . "\n";
	}
}

/**
 * 存储数据入库
 */
function batch_add_to_db($new_data) 
{
	$i = 0;
	$size = 50; //每次执行数据量
	
	$chunks = array_chunk($new_data, $size, true);

	$table_name = 'location';
	
	try {

		foreach ($chunks as $item) {
			$i++;
			$db = conn_mysql();
			$install_sql = 'INSERT INTO ' . $table_name . ' (num, city, code, province, area, cardtype, post_code) VALUES';
			$install_sql .= implode(',', $item);
			$install_sql .= ';';
			
			//执行
			$num = $db->exec($install_sql);
			//$insert_id = $db->lastInsertId();
			if ($num) {
				echo $i . '成功新增' . $num . '条记录' . "\n";
			} else {
				echo $i . '新增失败' . "\n";
				exit;
			}
			$db = null;
		}
		
	} catch(PDOException $e) {
		echo $e->getMessage();
		die();
	}
}